﻿using NawaData.Dao;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

namespace DatabaseCore
{
    public class ProdQueueDao : IProdQueueDao
    {
        //public bool ApproveOrder(string orderNo)
        //{
        //    using (var ctx = new ExportDBEntities())
        //    {
        //        var orderDetails = from x in ctx.tbl_OrderDetail
        //                           where x.OrderNo == orderNo
        //                           select x;
        //        List<ProdQueue> prodQueue = new List<ProdQueue>();
        //        foreach (var detail in orderDetails)
        //        {
        //            if (!string.IsNullOrEmpty(detail.MaterialNo))
        //            {
        //                prodQueue.Add(new ProdQueue
        //                {
        //                    ProductCode = detail.MaterialNo,
        //                    PlantCode = detail.Plant,
        //                    OrderNo = detail.OrderNo,
        //                    Qty = detail.RemainingQty, //detail.Qty, -> น่าจะใช้ค่า remaining ซึ่งตัด ATP ไปแล้วนำไปเข้าคิว?
        //                    Date = System.DateTime.Now,
        //                    Priority = 0,
        //                    IsActive = true
        //                });
        //            }
        //        }


        //        return InsertCollection(prodQueue);
        //    }
        //}

        public bool Insert(ProdQueue entity)
        {
            using (var ctx = new ExportDBEntities())
            {
                ctx.ProdQueue.Add(entity);
                return ctx.SaveChanges() > 0;
            }
        }

        public int ClearData()
        {

            using (var ctx = new ExportDBEntities())
            {
                return ctx.Database.ExecuteSqlCommand("delete ProdQueue where IsActive = 'true'");
            }
        }

        public bool InsertCollection(List<ProdQueue> entity)
        {
            using (var ctx = new ExportDBEntities())
            {
                foreach (var item in entity)
                {
                    ctx.ProdQueue.Add(item);
                }
                return ctx.SaveChanges() > 0;
            }
        }

        public List<ProdQueue> GetAllQueues()
        {
            using (var ctx = new ExportDBEntities())
            {
                var queues = (from q in ctx.ProdQueue
                              select q)
                              .OrderBy(x => x.ProductCode)
                              .ThenBy(x => x.Date)
                              .ThenBy(x => x.id).ToList();
                var orderIds = (from q in queues
                                select q.OrderNo).Distinct().ToArray();

                var orders = (from d in ctx.tbl_Order
                             where orderIds.Contains(d.OrderNo)
                             select d).ToList();

                var sp = (from specialPercent in ctx.ProductSpecialPercent
                          where specialPercent.IsActive == true && DateTime.Today >= specialPercent.StartDate && DateTime.Today <= specialPercent.EndDate
                          select specialPercent);
                

                foreach (var queue in queues)
                {
                    foreach (var s in sp)
                    {
                        if (queue.ProductCode == s.ProductCode)
                        {
                            queue.Rate = s.PercentSpecial;
                            if (s.EndDate >= DateTime.Today)
                            {
                                queue.ValidFrom = s.StartDate;
                                queue.ValidTo = s.EndDate;
                            }
                        }
                    }
                    foreach (var d in orders)
                    {
                        if (queue.OrderNo == d.OrderNo)
                        {
                            queue.RefOrderNo = d.RefOrderNo;
                        }
                    }
                }


                var productIds = queues.Select(q => q.ProductCode).Distinct().ToArray();
                var productDao = new ProductsDao();
                var productNames = productDao.getProductNames(productIds);
                foreach (var d in queues)
                {
                    foreach (var name in productNames)
                    {
                        if (d.ProductCode == name.mmatno)
                        {
                            d.ProductName = name.mmateng;
                        }
                    }
                }

                return queues.ToList();
            }
        }

        public bool UpdateQueuePause(List<ProdQueue> qs)
        {
            using (var ctx = new ExportDBEntities())
            {
                foreach (var q in qs)
                {
                    var queue = (from qq in ctx.ProdQueue
                                 where qq.id == q.id
                                 select qq).SingleOrDefault();

                    if (queue != null)
                    {
                        queue.IsActive = q.IsActive;
                    }
                }


                return ctx.SaveChanges() > 0;
            }
        }

        public bool UpdateQueuePause(long qid, string updateBy, bool isActive = true)
        {
            using (var ctx = new ExportDBEntities())
            {
                var queue = (from q in ctx.ProdQueue
                             where q.id == qid
                             select q).FirstOrDefault();

                if (queue == null || (queue.IsProcessing ?? false))
                    return false;
                queue.UpdateBy = updateBy;
                queue.IsActive = isActive;

                return ctx.SaveChanges() > 0;
            }
        }

        public bool UpdateQueuePriority(IList<ProdQueue> entityList)
        {
            using (var ctx = new ExportDBEntities())
            {
                foreach (var item in entityList)
                {
                    var queue = (from q in ctx.ProdQueue
                                 where q.id == item.id
                                 select q).FirstOrDefault();

                    if (queue == null || (queue.IsProcessing ?? false))
                        return false;

                    queue.Priority = item.Priority;
                }

                return ctx.SaveChanges() > 0;
            }
        }

        public bool Delete(ProdQueue entity)
        {
            using (var ctx = new ExportDBEntities())
            {
                ctx.ProdQueue.Remove(entity);
                return ctx.SaveChanges() > 0;
            }
        }

        public bool Delete(long id, string user)
        {
            using (var ctx = new ExportDBEntities())
            {
                var queue = (from q in ctx.ProdQueue
                             where q.id == id
                             select q).FirstOrDefault();

                if (queue == null || (queue.IsProcessing ?? false))
                    return false;
                var transaction = new tbl_Transaction()
                {
                    OrderNo = queue.OrderNo,
                    MaterialNo = queue.ProductCode,
                    Plant = "-",
                    Qty = 0,
                    UpdateBy = user,
                    UpdateDate = DateTime.Now,
                    Result = "Deleted Queue"
                };
                var qdetail = (from dd in ctx.tbl_OrderDetail
                               where dd.OrderNo == queue.OrderNo && dd.MaterialNo == queue.ProductCode
                               select dd).FirstOrDefault();
                if (qdetail != null)
                {
                    qdetail.IsDeleteFromQueue = true;
                }
                ctx.tbl_Transaction.Add(transaction);
                var orderDetailsProvider = new OrderDetailDao();

                //orderDetailsProvider.UpdateReceivedQtyWithMatNo(transaction.OrderNo, transaction.MaterialNo, Convert.ToInt32(queue.Qty), user);
                ctx.ProdQueue.Remove(queue);
                return ctx.SaveChanges() > 0;
            }
        }

        public bool DeleteByOrder(string OrderNo)
        {
            using (var ctx = new ExportDBEntities())
            {
                var result = ctx.Database.ExecuteSqlCommand("DELETE FROM ProdQueue WHERE OrderNo = @OrderNo", new[] { new SqlParameter("@OrderNo", OrderNo) });

                return result > 0;
            }
        }

        public bool UpdateChangePlantAndQty(List<ProdQueue> qs, string user)
        {
            using (var ctx = new ExportDBEntities())
            {
                foreach (var qupdate in qs)
                {
                    var queue = (from q in ctx.ProdQueue
                                 where q.id == qupdate.id
                                 select q).FirstOrDefault();

                    if (queue == null || (queue.IsProcessing ?? false))
                        return false;

                    if (qupdate.PlantCode != queue.PlantCode)
                    {
                        var msg = string.Format("Revised Plant from {0} to <b><font color='red'>{1}</font></b> by {2} On {3}", queue.PlantCode,
                            qupdate.PlantCode,
                            user,
                            DateTime.Now.ToString());
                        var transaction = new tbl_Transaction()
                        {
                            OrderNo = qupdate.OrderNo,
                            MaterialNo = qupdate.ProductCode,
                            Plant = "-",
                            Qty = 0,
                            UpdateBy = user,
                            UpdateDate = DateTime.Now,
                            Result = msg
                        };
                        ctx.tbl_Transaction.Add(transaction);
                        var orderDetailsProvider = new OrderDetailDao();

                        orderDetailsProvider.UpdateReceivedPlantWithMatNo(transaction.OrderNo, transaction.MaterialNo, qupdate.PlantCode, user);
                    }
                    if (qupdate.Qty != queue.Qty)
                    {
                        var msg = string.Format("Revised QTY from {0} to <b><font color='red'>{1}</font></b> by {2} On {3}", queue.Qty,
                            qupdate.Qty,
                            user,
                            DateTime.Now.ToString());
                        var transaction = new tbl_Transaction()
                        {
                            OrderNo = qupdate.OrderNo,
                            MaterialNo = qupdate.ProductCode,
                            Plant = "-",
                            Qty = -1 * Convert.ToInt32((queue.Qty - qupdate.Qty)),
                            UpdateBy = user,
                            UpdateDate = DateTime.Now,
                            Result = msg
                        };
                        ctx.tbl_Transaction.Add(transaction);

                        var orderDetailsProvider = new OrderDetailDao();

                        orderDetailsProvider.UpdateReceivedQtyWithMatNo(transaction.OrderNo, transaction.MaterialNo, Convert.ToInt32((queue.Qty - qupdate.Qty)), user);
                    }

                    if (qupdate.Qty == 0)
                    {
                        ctx.ProdQueue.Remove(queue);
                    }
                    else
                    {
                        queue.PlantCode = qupdate.PlantCode;
                        queue.Qty = qupdate.Qty;
                        queue.Date = DateTime.Now;
                        queue.UpdateBy = user;
                    }
                    
                }


                return ctx.SaveChanges() > 0;
            }
        }
    }
}